-- extended event session to monitor file size change
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'FileGrowth')
	DROP EVENT SESSION FileGrowth ON SERVER;
GO

-- check databases and growth increments after restores
SELECT 
	DB_NAME(database_id) AS database_name,
	name  AS 'file_name',
	type_desc AS file_type_desc, 
	physical_name, 
	size*8/1024 AS size_mb,
	growth*8/1024 AS growth_mb,
	state_desc
FROM master.sys.master_files
WHERE database_id > 4;

-- check the events in the default trace
SELECT
	DISTINCT(te.name) AS event_name
FROM ::fn_trace_geteventinfo(1) ti
INNER JOIN sys.trace_events te ON ti.eventID = te.trace_event_id
ORDER BY te.name;

/*
	Event session to track file growth
	(this only works for SQL Server 2012 and higher
	Start manually and then run scripts 3, 4, 5 and 6.
	Look at histogram data and file_target data
*/
CREATE EVENT SESSION FileGrowth ON SERVER 
	ADD EVENT 
		sqlserver.database_file_size_change
	ADD TARGET package0.event_file(
			SET FILENAME = N'C:\Temp\FileGrowth.xel'
		),
	ADD TARGET package0.histogram(
			SET FILTERING_EVENT_NAME = N'sqlserver.database_file_size_change',
			SLOTS = (10), SOURCE = N'database_id', SOURCE_TYPE = (0)
		)
	WITH (MAX_MEMORY = 4096 KB, MAX_DISPATCH_LATENCY = 30 SECONDS);
GO

-- enable event session
ALTER EVENT SESSION FileGrowth ON SERVER STATE = START;
GO

-- get database IDs when checking data
SELECT
	database_id,
	name
FROM sys.databases
ORDER BY database_id ASC;

-- monitor the workload

-- stop the event session
ALTER EVENT SESSION FileGrowth ON SERVER STATE = STOP;
GO

-- cleanup
DROP EVENT SESSION FileGrowth ON SERVER;
GO